const express = require('express');
const router = express.Router();
const db = require('../db'); // 使用统一的数据库连接

// 生成订单号
function generateOrderNo() {
  return 'ORD' + Date.now() + Math.random().toString(36).substr(2, 9).toUpperCase();
}

// 获取订单列表
router.get('/', (req, res) => {
  const { page = 1, pageSize = 10, buyer_id, seller_id, status } = req.query;
  const offset = (page - 1) * pageSize;
  
  let sql = `
    SELECT o.*, 
           t.title as textbook_title, t.images as textbook_images,
           u1.username as buyer_name, u2.username as seller_name
    FROM orders o
    LEFT JOIN textbooks t ON o.textbook_id = t.id
    LEFT JOIN users u1 ON o.buyer_id = u1.user_id
    LEFT JOIN users u2 ON o.seller_id = u2.user_id
    WHERE 1=1
  `;
  const params = [];
  
  if (buyer_id) {
    sql += ' AND o.buyer_id = ?';
    params.push(buyer_id);
  }
  
  if (seller_id) {
    sql += ' AND o.seller_id = ?';
    params.push(seller_id);
  }
  
  if (status) {
    sql += ' AND o.status = ?';
    params.push(status);
  }
  
  sql += ' ORDER BY o.created_at DESC LIMIT ? OFFSET ?';
  params.push(parseInt(pageSize), parseInt(offset));
  
  db.query(sql, params, (err, result) => {
    if (err) {
      console.error('Database error:', err);
      return res.status(500).json({ 
        error: 'Database error',
        message: err.message 
      });
    }
    
    // 解析images字段
    const data = result.map(item => {
      if (item.textbook_images) {
        try {
          item.textbook_images = JSON.parse(item.textbook_images);
        } catch (e) {
          item.textbook_images = [];
        }
      }
      return item;
    });
    
    // 获取总数
    let countSql = 'SELECT COUNT(*) as total FROM orders WHERE 1=1';
    const countParams = [];
    
    if (buyer_id) {
      countSql += ' AND buyer_id = ?';
      countParams.push(buyer_id);
    }
    if (seller_id) {
      countSql += ' AND seller_id = ?';
      countParams.push(seller_id);
    }
    if (status) {
      countSql += ' AND status = ?';
      countParams.push(status);
    }
    
    db.query(countSql, countParams, (countErr, countResult) => {
      if (countErr) {
        console.error('Count error:', countErr);
        return res.json({
          success: true,
          data: data,
          pagination: {
            page: parseInt(page),
            pageSize: parseInt(pageSize),
            total: 0
          }
        });
      }
      
      res.json({
        success: true,
        data: data,
        pagination: {
          page: parseInt(page),
          pageSize: parseInt(pageSize),
          total: countResult[0].total
        }
      });
    });
  });
});

// 根据ID获取订单详情
router.get('/:id', (req, res) => {
  const { id } = req.params;
  const sql = `
    SELECT o.*, 
           t.title as textbook_title, t.author as textbook_author, 
           t.images as textbook_images, t.description as textbook_description,
           u1.username as buyer_name, u1.phone as buyer_phone, u1.address as buyer_address,
           u2.username as seller_name, u2.phone as seller_phone
    FROM orders o
    LEFT JOIN textbooks t ON o.textbook_id = t.id
    LEFT JOIN users u1 ON o.buyer_id = u1.user_id
    LEFT JOIN users u2 ON o.seller_id = u2.user_id
    WHERE o.id = ?
  `;
  
  db.query(sql, [id], (err, result) => {
    if (err) {
      console.error('Database error:', err);
      return res.status(500).json({ 
        error: 'Database error',
        message: err.message 
      });
    }
    
    if (result.length === 0) {
      return res.status(404).json({ 
        error: 'Order not found',
        message: `Order with ID ${id} does not exist`
      });
    }
    
    const order = result[0];
    if (order.textbook_images) {
      try {
        order.textbook_images = JSON.parse(order.textbook_images);
      } catch (e) {
        order.textbook_images = [];
      }
    }
    
    res.json({
      success: true,
      data: order
    });
  });
});

// 创建订单
router.post('/', (req, res) => {
  const { buyer_id, seller_id, textbook_id, price, buyer_address, buyer_phone, buyer_name, remark } = req.body;
  
  if (!buyer_id || !seller_id || !textbook_id || !price) {
    return res.status(400).json({
      error: 'Validation error',
      message: 'buyer_id, seller_id, textbook_id and price are required'
    });
  }
  
  // 检查教材是否存在且可售
  const checkTextbookSql = 'SELECT * FROM textbooks WHERE id = ? AND status = "on_sale"';
  db.query(checkTextbookSql, [textbook_id], (checkErr, checkResult) => {
    if (checkErr) {
      console.error('Database error:', checkErr);
      return res.status(500).json({ 
        error: 'Database error',
        message: checkErr.message 
      });
    }
    
    if (checkResult.length === 0) {
      return res.status(400).json({
        error: 'Validation error',
        message: 'Textbook is not available for sale'
      });
    }
    
    const order_no = generateOrderNo();
    const sql = `
      INSERT INTO orders 
      (order_no, buyer_id, seller_id, textbook_id, price, buyer_address, buyer_phone, buyer_name, remark, status) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `;
    const values = [order_no, buyer_id, seller_id, textbook_id, price, buyer_address || null, buyer_phone || null, buyer_name || null, remark || null];
    
    db.query(sql, values, (err, result) => {
      if (err) {
        console.error('Database error:', err);
        return res.status(500).json({ 
          error: 'Database error',
          message: err.message 
        });
      }
      
      // 更新教材状态为已售
      const updateTextbookSql = 'UPDATE textbooks SET status = "sold" WHERE id = ?';
      db.query(updateTextbookSql, [textbook_id]);
      
      res.status(201).json({
        success: true,
        message: 'Order created successfully',
        data: {
          id: result.insertId,
          order_no,
          buyer_id,
          seller_id,
          textbook_id,
          price
        }
      });
    });
  });
});

// 更新订单状态
router.put('/:id/status', (req, res) => {
  const { id } = req.params;
  const { status } = req.body;
  
  const validStatuses = ['pending', 'paid', 'shipped', 'completed', 'cancelled'];
  if (!status || !validStatuses.includes(status)) {
    return res.status(400).json({
      error: 'Validation error',
      message: 'Valid status is required'
    });
  }
  
  const sql = 'UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?';
  
  db.query(sql, [status, id], (err, result) => {
    if (err) {
      console.error('Database error:', err);
      return res.status(500).json({ 
        error: 'Database error',
        message: err.message 
      });
    }
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ 
        error: 'Order not found',
        message: `Order with ID ${id} does not exist`
      });
    }
    
    // 如果订单被取消，将教材状态改回在售
    if (status === 'cancelled') {
      const getOrderSql = 'SELECT textbook_id FROM orders WHERE id = ?';
      db.query(getOrderSql, [id], (orderErr, orderResult) => {
        if (!orderErr && orderResult.length > 0) {
          const updateTextbookSql = 'UPDATE textbooks SET status = "on_sale" WHERE id = ?';
          db.query(updateTextbookSql, [orderResult[0].textbook_id]);
        }
      });
    }
    
    res.json({
      success: true,
      message: 'Order status updated successfully'
    });
  });
});

// 取消订单
router.put('/:id/cancel', (req, res) => {
  const { id } = req.params;
  
  const sql = 'UPDATE orders SET status = "cancelled", updated_at = NOW() WHERE id = ? AND status = "pending"';
  
  db.query(sql, [id], (err, result) => {
    if (err) {
      console.error('Database error:', err);
      return res.status(500).json({ 
        error: 'Database error',
        message: err.message 
      });
    }
    
    if (result.affectedRows === 0) {
      return res.status(400).json({ 
        error: 'Cannot cancel order',
        message: 'Order cannot be cancelled or does not exist'
      });
    }
    
    // 将教材状态改回在售
    const getOrderSql = 'SELECT textbook_id FROM orders WHERE id = ?';
    db.query(getOrderSql, [id], (orderErr, orderResult) => {
      if (!orderErr && orderResult.length > 0) {
        const updateTextbookSql = 'UPDATE textbooks SET status = "on_sale" WHERE id = ?';
        db.query(updateTextbookSql, [orderResult[0].textbook_id]);
      }
    });
    
    res.json({
      success: true,
      message: 'Order cancelled successfully'
    });
  });
});

module.exports = router;

